package com.ruoyi.lowcode.sqlgenerator;

import cn.hutool.core.util.ObjectUtil;
import com.alibaba.fastjson2.JSON;
import com.alibaba.fastjson2.JSONArray;
import com.alibaba.fastjson2.JSONObject;
import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.ruoyi.common.core.domain.PageQuery;
import com.ruoyi.common.utils.StringUtils;
import com.ruoyi.lowcode.database.DatabaseMain;
import com.ruoyi.lowcode.database.SourceFactory;
import com.ruoyi.lowcode.domain.LcField;
import com.ruoyi.lowcode.domain.LcModel;
import com.ruoyi.lowcode.domain.bo.ModelRowBo;
import com.ruoyi.lowcode.domain.bo.SelectListModelBo;
import com.ruoyi.lowcode.domain.vo.LcDatabaseSourceVo;
import com.ruoyi.lowcode.domain.vo.LcFieldVo;
import com.ruoyi.lowcode.enums.MysqlConditionalConstructor;
import com.ruoyi.lowcode.utils.ConverterUtils;
import net.sf.jsqlparser.JSQLParserException;
import net.sf.jsqlparser.parser.CCJSqlParserUtil;
import net.sf.jsqlparser.statement.select.PlainSelect;
import net.sf.jsqlparser.statement.select.Select;
import org.apache.commons.lang3.ObjectUtils;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * mysql sql 生成器
 */
public class MysqlGenerator implements GeneratorFactory{

    @Override
    public List<String> insert(LcDatabaseSourceVo databaseSource, List<LcFieldVo> fieldList, ModelRowBo modelRowBo, Map<String, String> tableIds) {

        List<String> sqlList = new ArrayList<>();

        Map<String, String> tableInsert = new HashMap<>();
        Map<String, String> tableInsertValue = new HashMap<>();

        for (LcFieldVo fieldVo : fieldList){

            if(StringUtils.isNotBlank(tableInsert.get(fieldVo.getTableName())) && StringUtils.isNotBlank(tableInsertValue.get(fieldVo.getTableName()))){

                if(ObjectUtils.isNotEmpty(modelRowBo.getData().get(fieldVo.getFieldAlias()))){
                    tableInsert.put(fieldVo.getTableName(), tableInsert.get(fieldVo.getTableName()) + fieldVo.getFieldName() + ",");
                    if(modelRowBo.getData().get(fieldVo.getFieldAlias()) instanceof ArrayList){
                        tableInsertValue.put(fieldVo.getTableName(), tableInsertValue.get(fieldVo.getTableName()) + "'"
                                + modelRowBo.getData().getJSONArray(fieldVo.getFieldAlias()).toJSONString() + "',");
                    }else{
                        tableInsertValue.put(fieldVo.getTableName(), tableInsertValue.get(fieldVo.getTableName()) + "'"
                                + modelRowBo.getData().get(fieldVo.getFieldAlias()) + "',");
                    }
                }

                continue;
            }

            if(ObjectUtils.isNotEmpty(modelRowBo.getData().get(fieldVo.getFieldAlias()))){
                tableInsert.put(fieldVo.getTableName(), "INSERT INTO " + fieldVo.getTableName() + "(" + fieldVo.getFieldName() + ",");
                if(modelRowBo.getData().get(fieldVo.getFieldAlias()) instanceof ArrayList){
                    tableInsertValue.put(fieldVo.getTableName(), " VALUES ('"+
                            modelRowBo.getData().getJSONArray(fieldVo.getFieldAlias()).toJSONString() + "',");
                }else{
                    tableInsertValue.put(fieldVo.getTableName(), " VALUES ('"+ modelRowBo.getData().get(fieldVo.getFieldAlias()) + "',");
                }
            }

        }

        for (String key : tableInsert.keySet()) {
            // 表主键设置自动递增，不设置自动递增，可以在这里手动设置
            // 创建人创建时间这些信息也在这里手动拼接
            String insertSql = tableInsert.get(key).substring(0, tableInsert.get(key).length() - 1) + ")"
                    + tableInsertValue.get(key).substring(0, tableInsertValue.get(key).length() - 1) + ")";

            sqlList.add(insertSql);
        }

        return sqlList;
    }

    @Override
    public List<String> update(LcDatabaseSourceVo databaseSource, List<LcFieldVo> fieldList, ModelRowBo modelRowBo, Map<String, String> tableIds) {

        List<String> sqlList = new ArrayList<>();

        Map<String, String> tableUpdate = new HashMap<>();

        for (LcFieldVo fieldVo : fieldList){
            if(StringUtils.isNotBlank(tableUpdate.get(fieldVo.getTableName()))){
                if(ObjectUtils.isNotEmpty(modelRowBo.getData().get(fieldVo.getFieldAlias()))){
                    if(modelRowBo.getData().get(fieldVo.getFieldAlias()) instanceof ArrayList){
                        tableUpdate.put(fieldVo.getTableName(), tableUpdate.get(fieldVo.getTableName())
                                + " " + fieldVo.getFieldName() + " = '" +
                                modelRowBo.getData().getJSONArray(fieldVo.getFieldAlias()).toJSONString() + "',");
                    }else {
                        tableUpdate.put(fieldVo.getTableName(), tableUpdate.get(fieldVo.getTableName())
                                + " " + fieldVo.getFieldName() + " = '" + modelRowBo.getData().get(fieldVo.getFieldAlias()) + "',");
                    }
                }
                continue;
            }

            if(ObjectUtils.isNotEmpty(modelRowBo.getData().get(fieldVo.getFieldAlias()))){

                if(modelRowBo.getData().get(fieldVo.getFieldAlias()) instanceof ArrayList){
                    tableUpdate.put(fieldVo.getTableName(), "UPDATE " + fieldVo.getTableName()
                            + " SET " + fieldVo.getFieldName() + " = '" +
                            modelRowBo.getData().getJSONArray(fieldVo.getFieldAlias()).toJSONString() + "',");
                }else {
                    tableUpdate.put(fieldVo.getTableName(), "UPDATE " + fieldVo.getTableName()
                            + " SET " + fieldVo.getFieldName() + " = '" + modelRowBo.getData().get(fieldVo.getFieldAlias()) + "',");
                }
            }
        }

        for (String key : tableUpdate.keySet()) {
            String updateSql = tableUpdate.get(key).substring(0, tableUpdate.get(key).length() - 1)
                    + " WHERE " + tableIds.get(key) + " = " + modelRowBo.getRowInfo().get(tableIds.get(key));
            sqlList.add(updateSql);
        }

        return sqlList;
    }

    @Override
    public String delete(String tableName, String fieldName, Object fieldValue) {

        String deleteSql = "DELETE FROM " + tableName + " WHERE "
                + fieldName + " = '" + fieldValue + "'";

        return deleteSql;
    }

    @Override
    public String paging(PageQuery pageQuery) {

        String pageSql = "";

        // 分页处理
        if(ObjectUtil.isNotNull(pageQuery)){
            pageSql += " LIMIT "
                    + (pageQuery.getPageNum() - 1) * pageQuery.getPageSize() + ","
                    + pageQuery.getPageSize();
        }

        return pageSql;
    }

    @Override
    public String conditionalSql(SelectListModelBo selectListModelBo, LcModel model, List<LcField> fieldList) throws JSQLParserException {
        String conditionalSql = "";

        // 条件处理
        if(ObjectUtil.isNotNull(selectListModelBo.getParams())){
            Select select = (Select) CCJSqlParserUtil.parse(model.getSelectSql());
            PlainSelect plainSelect = (PlainSelect) select.getSelectBody();

            // sql是否存在where
            if (ObjectUtil.isNull(plainSelect.getWhere())) {
                conditionalSql += " where 1=1";
            }

            for (LcField field : fieldList) {
                Object selectValue = selectListModelBo.getParams().get(field.getFieldAlias());
                if ("DATE".equals(field.getSelectType()) || "DATETIME".equals(field.getSelectType())) {
                    if (ObjectUtils.isNotEmpty(selectValue)) {
                        List<String> dates = ConverterUtils.objToList(selectValue);
                        Map<String, Object> map = new HashMap<>();
                        map.put("fieldTableAliasName", field.getFieldTableAliasName());
                        map.put("startValue", dates.get(0));
                        map.put("endValue", dates.get(1));
                        conditionalSql += MysqlConditionalConstructor.valueOf("DATE").getConditionalString(map);
                    }
                } else {
                    // 搜索条件有值
                    if (ObjectUtils.isNotEmpty(selectValue) && StringUtils.isNotBlank(field.getSelectType())) {
                        Map<String, Object> map = new HashMap<>();
                        map.put("fieldTableAliasName", field.getFieldTableAliasName());
                        map.put("value", selectValue);
                        conditionalSql += MysqlConditionalConstructor.valueOf(field.getSelectType()).getConditionalString(map);
                    }
                }
            }
        }

        return conditionalSql;
    }

    @Override
    public String getSelectFieldSql(List<LcFieldVo> queryFieldList) {
        String selectSql = "select ";

        for (LcFieldVo fieldVo : queryFieldList){
            selectSql += " " + fieldVo.getFieldTableAliasName() + " AS " + fieldVo.getFieldAlias() + ",";
        }
        selectSql = selectSql.substring(0, selectSql.length() - 1);

        return selectSql;
    }

    @Override
    public String getSelectWhereSql(String selectSql) {
        return selectSql.toUpperCase().substring(selectSql.toLowerCase().indexOf("from"));
    }

    @Override
    public String getCountFieldSql(String field, int type) {
        if(1 == type){
            return "select SUM(" + field + ") AS num ";
        }else {
            return "select COUNT(" + field + ") AS num ";
        }
    }


}
